﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using MySql.Data.MySqlClient;

namespace WHLRDF.ORM.MySql
{
    /// <summary>
    /// MySQL数据库引擎帮助类
    /// </summary>
    public class DataAccess
    {
        private static DataAccess _mySqlDataAccess;
        private static object _locker = new object();
        public static DataAccess Instance
        {
            get
            {
                if (_mySqlDataAccess == null)
                {
                    lock (_locker)
                    {

                        if (_mySqlDataAccess == null)
                        {
                            _mySqlDataAccess = new DataAccess();
                        }
                    }
                }
                return _mySqlDataAccess;
            }

        }

        #region public string SqlSafe(string value) 检查参数的安全性
        /// <summary>
        /// 检查参数的安全性
        /// </summary>
        /// <param name="value">参数</param>
        /// <returns>安全的参数</returns>
        public string SqlSafe(string value)
        {
            value = value.Replace("'", "''");
            // value = value.Replace("%", "'%");
            return value;
        }
        #endregion

        #region public DbParameter MakeInParam(string targetFiled, object targetValue)
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="targetFiled">目标字段</param>
        /// <param name="targetValue">值</param>
        /// <returns>参数</returns>
        public DbParameter MakeInParam(string targetFiled, object targetValue)
        {
            return new MySqlParameter(targetFiled, targetValue);
        }
        #endregion


        #region public DbParameter[] MakeParameters(string targetFiled, object targetValue)
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="targetFiled">目标字段</param>
        /// <param name="targetValue">值</param>
        /// <returns>参数集</returns>
        public DbParameter[] MakeParameters(string targetFiled, object targetValue)
        {
            DbParameter[] dbParameters = null;
            if (targetFiled != null && targetValue != null)
            {
                dbParameters = new DbParameter[1];
                dbParameters[0] = this.MakeInParam(targetFiled, targetValue);
            }
            return dbParameters;
        }
        #endregion

        #region public DbParameter[] MakeParameters(string[] targetFileds, Object[] targetValues)
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="targetFiled">目标字段</param>
        /// <param name="targetValue">值</param>
        /// <returns>参数集</returns>
        public DbParameter[] MakeParameters(string[] targetFileds, Object[] targetValues)
        {
            DbParameter[] dbParameters = new DbParameter[0];
            if (targetFileds != null && targetValues != null)
            {
                dbParameters = new DbParameter[targetFileds.Length];
                for (int i = 0; i < targetFileds.Length; i++)
                {
                    if (targetFileds[i] != null && targetValues[i] != null)
                    {
                        dbParameters[i] = this.MakeInParam(targetFileds[i], targetValues[i]);
                    }
                }
            }
            return dbParameters;
        }
        #endregion

        public DbParameter MakeParam(string paramName, DbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            MySqlParameter param = new MySqlParameter(paramName, Value);

            if (Size > 0)
            {
                param.Size = Size;
            }

            param.DbType = DbType;
            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
                param.Value = Value;

            return param;
        }
       
        #region public string GetParameter(string parameter) 获得参数Sql表达式
        /// <summary>
        /// 获得参数Sql表达式
        /// </summary>
        /// <param name="parameter">参数名称</param>
        /// <returns>字符串</returns>
        public string GetParameter(string parameter)
        {
            return " ?" + parameter;
        }

        public string GetLikeParameter(string parameter)
        {
            return " concat('%',?" + parameter + ",'%')";
        }
        #endregion

        #region string PlusSign(params string[] values)
        /// <summary>
        ///  获得Sql字符串相加符号
        /// </summary>
        /// <param name="values">参数值</param>
        /// <returns>字符加</returns>
        public string PlusSign(params string[] values)
        {
            string returnValue = string.Empty;
            returnValue = " CONCAT(";
            for (int i = 0; i < values.Length; i++)
            {
                returnValue += values[i] + " ,";
            }
            returnValue = returnValue.Substring(0, returnValue.Length - 2);
            returnValue += ")";
            return returnValue;
        }
        #endregion

        #region
        public DbConnection GetConnection(string connectionString)
        {
            return new MySqlConnection(connectionString);
        }

        public DbDataAdapter GetDataAdapter()
        {
            return new MySqlDataAdapter();
        }

        public string Now()
        {
            return "select now()";
        }

        public DbParameter[] GetParameter(List<DataParameter> parameters)
        {
            if (parameters != null && parameters.Count > 0)
            {
                DbParameter[] parameter = new DbParameter[parameters.Count];
                var index = 0;
                foreach (var item in parameters)
                {
                    parameter[index] = this.MakeParam(item.ParameterName, item.DbType, item.Size, item.Direction, item.Value);
                    index++;

                }
                return parameter;
            }
            return null;
        }

        public DbParameter GetParameter(string name, object value)
        {
            return this.MakeInParam(name, value);
        }
        public string Identity(string where)
        {
            return "select @@IDENTITY;";
        }
        #endregion

        public void SqlBulkCopyData(DataTable dataTable)
        {

        }
        /// <summary>
        /// 禁用自增
        /// </summary>
        /// <returns></returns>
        public string Identity_Disabled(string tableName)
        {
            return "";
        }
        /// <summary>
        /// 启用自增
        /// </summary>
        /// <returns></returns>
        public string Identity_Enable(string tableName)
        {
            return "";
        }

      
        public string QueryPage(string hql, Order[] orders,
            int pageIndex, int pageSize)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append(" " + hql + " {0}   ");
            string strOrder = "";
            int fristIndex = (pageIndex - 1) * pageSize;
            int maxIndex = pageIndex * pageSize;
            if (orders != null && orders.Length > 0)
            {
                int index = 0;
                foreach (var order in orders)
                {
                    if (order == null)
                        continue;
                    if (index >= 1)
                        strOrder += ",";
                    else
                        strOrder = " order by ";
                    index++;
                    strOrder += order.ToString();
                }
            }
            strsql.Append(strOrder + (pageIndex > 0 ? " limit " + fristIndex + "," + pageSize.ToString() : ""));
            return strsql.ToString();
        }

        public string RecordCount(string hql, bool createSql = true)
        {
            if (!createSql)
            {
                return hql;
            }
            return " select count(1) from (" + hql + " {0}) as temptable";
        }
        /// 获取统计查询总数Sql
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public string GetRecordCountSql(string tableName)
        {
            return " select count(1) from " + tableName + " where 1=1 {0}";
        }
        /// <summary>
        /// 获取查询表Sql
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="queryFieldBuilder"></param>
        /// <param name="isDeleted"></param>
        /// <returns></returns>
        public string GetSelectSql(string tableName, string queryFieldBuilder = "*", bool isDeleted = true)
        {
            return "select " + queryFieldBuilder + " from " + tableName + " where 1=1 " + (isDeleted ? " and " + EntityBase.__IsDeleted + "=0 " : "")+" {0} " ;
        }
        #region idbtype
        /// <summary>
        /// 获取数据源中的表或者视图
        /// </summary>
        /// <param name="dbName">服务器名称</param>
        /// <param name="dbType">U表示表 V表示视图</param>
        /// <returns></returns>
        public string GetTableOrView(string dbName, string dbType = "U")
        {
            return @" Select 0 as DbTableId,Table_Name as TableName,Table_Name as  DbTableCation,Table_Name as  DbTableCationEn,case when Table_Type='BASE TABLE' then 1 else 0 end as IsTable 
                 FROM information_schema.tables Where Table_Type='"+(dbType=="V"?"View": "BASE TABLE") +"' and table_schema='" + dbName + "' orDER BY Table_Name  ";
        }

        public string GetDbColumn(string tableName, string dbServerName)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append(@"
                SELECT  
                ordinal_position as NNumber,
                Column_Name FieldName,
                case when extra='auto_increment' then 1 else 0 end as IsIdentity,   
                case   when   column_key='PRI'   then   1   else   0   end as IsPrimary,   
                data_Type as FieldType,   
                 case when character_maximum_length>0 then character_maximum_length else numeric_precision end as ByteLength,   
                 case when character_maximum_length>0 then character_maximum_length else numeric_precision end NLength,   
                 case when numeric_scale>0 then numeric_scale else 0 end as NScale,   
                case when is_nullable='NO' then 0 else 1 end  as NIsNull,   
                column_default as DefaultValue,
                 column_Comment as Comments,
                data_Type as DbTypeName      
                FROM   information_schema.columns     
                where   table_name='" + tableName + "' and table_schema='" + dbServerName + "' Order   by ordinal_position");
            return strsql.ToString();
        }
        public string GetSource(string tableName)
        {
            return "select * from " + tableName + " where 1=1";
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dbColumns"></param>
        /// <returns></returns>
        public string CreateTable(string tableName, List<DbColumnAttribute> dbColumns)
        {
            if (dbColumns == null || dbColumns.Count <= 0)
            {
                return "";
            }

            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("CREATE TABLE " + tableName);
            stringBuilder.Append("(");
            var index = 0;
            foreach (var item in dbColumns)
            {
                index++;
                if (index > 1)
                {
                    stringBuilder.Append(",");
                }
                stringBuilder.Append("\r\n " + item.Name + " " + GetColumnType(item) + " " + (item.Identifier ? " auto_increment" : "") + (item.IsPrimaryKey ? " primary key" : "") + (!item.AllowDBNull ? " not null" : "") + GetColumnDefault(item));
            }
            stringBuilder.Append(")");
            return stringBuilder.ToString();
        }
        /// <summary>
        /// 获取字段类型
        /// </summary>
        /// <param name="dbColumn"></param>
        /// <returns></returns>
        private string GetColumnType(DbColumnAttribute dbColumn)
        {
            switch ((SysPropertyType)dbColumn.SysFieldTypeId)
            {
                case SysPropertyType.String:
                    if (dbColumn.MaxLength <= 0)
                    {
                        return  " text";
                    }
                //case SysPropertyType.Ntext:
                    return dbColumn.ColumnType.Replace("n","") + "(" +((dbColumn.MaxLength!=-1)? dbColumn.MaxLength.ToString():"max") + ")";
                case SysPropertyType.Decimal:
                    return dbColumn.ColumnType + "(" + dbColumn.MaxLength.ToString() + "," + dbColumn.Scale.ToString() + ")";
                default:
                    return dbColumn.ColumnType;
            }
        }
        /// <summary>
        /// 构建字段默认值
        /// </summary>
        /// <param name="dbColumn"></param>
        /// <returns></returns>
        private string GetColumnDefault(DbColumnAttribute dbColumn)
        {
            if (string.IsNullOrWhiteSpace(dbColumn.Default))
            {
                return "";
            }
            string defaultValue = "";
            switch ((SysPropertyType)dbColumn.SysFieldTypeId)
            {
                case SysPropertyType.String:
                case SysPropertyType.Ntext:
                    defaultValue= "'"+dbColumn.Default.Replace("'","")+"'";
                    if (defaultValue.ToLower() == "newid")
                    {
                        defaultValue = " newid()";
                    }
                    break;
                case SysPropertyType.DateTime:
                    defaultValue = " now()";
                    break;
                
                case SysPropertyType.Guid:
                    defaultValue = " newid()";
                    break;
                default:
                    defaultValue=  dbColumn.Default;
                    break;
            }
            if (!string.IsNullOrWhiteSpace(defaultValue))
            {
                
                defaultValue = " default " + defaultValue;
            }
            return defaultValue;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnItem"></param>
        /// <returns></returns>
        public string AddColumn(string tableName, DbColumnAttribute columnItem)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("alter table " + tableName + " add  " + columnItem.Name + " " + GetColumnType(columnItem) + " " + (columnItem.Identifier ? " auto_increment" : "") + (columnItem.IsPrimaryKey ? " primary key" : "") + (!columnItem.AllowDBNull ? " not null" : "") + GetColumnDefault(columnItem) + ";");
            return stringBuilder.ToString();
        }

        public string DeleteTable(string table)
        {
            StringBuilder stringBuilder = new StringBuilder();
            return stringBuilder.ToString();
        }

        public string DeleteColumn(string table, string columnName)
        {
            StringBuilder stringBuilder = new StringBuilder();
            return stringBuilder.ToString();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string UpdateDescriptionSql(string tableName, string columnName, string description, bool isAdd)
        {
            string stroeName = "sys.sp_addextendedproperty";
            if (string.IsNullOrWhiteSpace(description) && !isAdd)//值为空 并且为修改时
            {
                stroeName = "sys.sp_dropextendedproperty";
            }
            else if (!isAdd)
            {
                stroeName = "sys.sp_updateextendedproperty";
            }
            StringBuilder sqlBuilder = new StringBuilder("EXEC " + stroeName + " ");
            sqlBuilder.Append("@name=N'MS_Description',");
            if (!string.IsNullOrWhiteSpace(description))
            {
                sqlBuilder.Append("@value=N'" + description + "',");
            }
            sqlBuilder.Append("@level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'" + tableName + "',@level2type = N'COLUMN', @level2name = N'" + columnName + "'");
            return sqlBuilder.ToString();
        }
        /// <summary>
        /// 判断描述是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">字段名</param>
        /// <returns></returns>
        public string ExistDescriptionSql(string tableName, string columnName)
        {
            StringBuilder stringBuilder = new StringBuilder(@"select 1  FROM   [syscolumns]   a     
                    inner   join [sysobjects]   d   on   a.id = d.id   and   d.xtype = 'U'   and   d.name <> 'dtproperties'
                   inner JOIN   sys.extended_properties ex    on   ex.major_id = a.id AND ex.minor_id = a.colid  AND ex.name = 'MS_Description'
                  where   d.name = '" + tableName + "' and a.name='" + columnName + "' Order   by a.id, a.colorder");
            return stringBuilder.ToString();
        }
        #endregion
    }
}